package main.java.utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * 学生管理类
 */
public class StudentManager {
    /**
     * 查询学生列表
     */
    public List<Student> findAll(){
        MyDBUtil myDBUtil = new MyDBUtil();
        Connection connection = myDBUtil.getConnection();
        List<Student> studentList = new ArrayList<>();
        try {
            Statement statement = connection.createStatement();
            String sql = "select * from student";
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()){ // 判断是否还有下一个元素，返回boolean类型
                Student student = new Student();
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String sex = resultSet.getString("sex");
                int age = resultSet.getInt("age");
                student.setName(name);
                student.setSex(sex);
                student.setAge(age);
                studentList.add(student);
            }
        }catch (Exception e){
            throw new RuntimeException("查询学生信息失败",e);
        }
        return studentList;
    }

    /**
     * 添加学生:接收学生参数s
     * @param s
     */
    public void addStu(Student s){
        Connection con = null;
        try {
            MyDBUtil myDBUtil = new MyDBUtil();
            con = myDBUtil.getConnection();
            //Statement statement = con.createStatement();
            String sql = "insert into student(name,sex,clas,grade,age) values(?,?,?,?,?)";
            PreparedStatement pre = con.prepareStatement(sql);
            pre.setString(1,s.getName());
            pre.setString(2,s.getSex());
            pre.setString(3,s.getClas());
            pre.setString(4,s.getGrade());
            pre.setInt(5,s.getAge());
            int n = pre.executeUpdate();
            System.out.print("成功插入数据库行数=："+n);
            System.out.println("\nsql:"+sql);
        }catch (Exception e){
            throw new RuntimeException("保存学生信息失败",e);
        }finally {
            MyDBUtil.close(con);
        }
    }

    /**
     * 删除学生
     * @param id
     */
    public void deleteStu(int id){
        Connection con = null;
        try {
            MyDBUtil myDBUtil = new MyDBUtil();
            con = myDBUtil.getConnection();
            //Statement statement = con.createStatement();
            String sql = "delete from student where id = ?";
            PreparedStatement pre = con.prepareStatement(sql);
            pre.setInt(1,id);
            int n = pre.executeUpdate();
            System.out.print("受影响的行数="+n+"\n");
        }catch (Exception e){
            throw new RuntimeException("删除学生失败");
        }finally {
            MyDBUtil.close(con);
        }
    }



    public static void main(String[] args) {
        StudentManager studentManager = new StudentManager();
        Student student = new Student();
        student.setAge(18);
        student.setSex("F");
        student.setName("麦克");
        student.setClas("计算机软件1班");
        student.setGrade("软件1班");
        //studentManager.addStu(student);
        studentManager.deleteStu(23);
        studentManager.findAll();
    }
}
